using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Windows.Forms;
using System.Collections;
//using SQLiteWrapper;
using System.Data;
using System.Globalization;
using System.Data.SQLite;
using System.Diagnostics;

namespace Pix2Gallery2
{
    class PSE6Interface
    {
        #region Declarations
        private string DBPath;
        //static private SQLiteBase PSEWrapperConn = new SQLiteBase();
        static private SQLiteConnection PSEConn = new SQLiteConnection();
        private Gallery2Interface G2int;
        private NameValueCollection AlbumImageList = new NameValueCollection();
        const string strTNColl = "collection_ns";
        const string strTNSmartColl = "smartcollect_ns";
        const string strTagTypeNameSmartColl = "smartcoll";
        const string strTNUserNS = "user_ns";
        const string strTNStackNS = "stack_ns";
        const string strTNPeople = "People";
        const string strTNPlaces = "Places";
        const string strTNEvents = "Events";
        const string strTNOther = "Other";
        const string strTNHidden = "Hidden";
        const string strTagTypeNameHidden = "hidden";
        const string strRatingMetadataIdentifier = "xmp:Rating";
        const string strCaptionMetadataIdentifier = "tiff:ImageDescription";
        const string strNotesMetadataIdentifier = "album:notes";
        private int intRatingMetadataId = 0;
        private int intHiddenTagId = 0;
        private int intCaptionMetadataId = 0;
        private int intNotesMetadataid = 0;
        private int intCollTopLevelId;
        private int intSmartCollTopLevelId;
        private int intSmartCollFirstLevelId;
        private int intUserTopLevelId;
        private int intCurrentCollTopLevelId;
        private ArrayList arlPSEAlbumId = new ArrayList();

        //20081031 TED - Commenting out to remove a warning
        //private NameValueCollection albumList;

        private NameValueCollection albumMediaList = new NameValueCollection();
        private DataTable dtMediaAlbumInfo;

        //20081031 TED - Commenting out to remove a warning
        //private DataTable dtGalleryAlbumInfo;
        
        private GalleryAlbum gaBase;
        #endregion Declarations

        #region ConnectionStuff
        /// <summary>
        /// Connects to the database specified.
        /// </summary>
        /// <param name="DBPath">Path of the Adobe Photoshop Elements 6 database file</param>
        /// <param name="g2Int">Gallery2Interface (must already be connected to a gallery 2 server)</param>
        public void ConnectToPSEDB(string DBPath, Gallery2Interface g2Int)
        {
            this.DBPath = DBPath;
            try
            {
                PSEConn.ConnectionString = string.Format("Data Source={0}", DBPath);
                PSEConn.Open();
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while connecting to database: " + DBPath +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in ConnectToPSEDB while connecting to database: >" + DBPath + "< " +
                    " Exception: " + exc.Message);
            }
            //PSEWrapperConn.OpenDatabase(DBPath);
            this.G2int = g2Int;
            //PopulateIntHiddenTagId();


        }
        public void DisconnectFromPSEDB()
        {
            if (AlbumImageList != null)
                AlbumImageList.Clear();
            this.DBPath = "";
            //PSEConn.CloseDatabase();
        }
        #endregion ConnectionStuff

        public void PopulateCollectionTree(ref System.Windows.Forms.TreeView tvAlbums)
        {
            //20080909 wcf initialize ALL of the tag identifiers early so I will have them for the main selects
            intHiddenTagId = GetTagId(strTNHidden, strTagTypeNameHidden);
            intRatingMetadataId = getMetadataId(strRatingMetadataIdentifier);
            intNotesMetadataid = getMetadataId(strNotesMetadataIdentifier);
            intCaptionMetadataId = getMetadataId(strCaptionMetadataIdentifier);
            intSmartCollTopLevelId = GetTagId(strTNSmartColl, 0);
            intSmartCollFirstLevelId = GetTagId(intSmartCollTopLevelId, 0);// the "Last 6 Months" node
            /*
            20080912 wcf Note that the name "Last 6 Months" can be changed in PSE6, but I don't see anywhere to change the Select that creates it
            so I have left the treatment here to 6 months
            */
            intCollTopLevelId = GetTagId(strTNColl, 0);
            PopulategaBase(); //This will retrieve the gallery information from Gallery2. 21/10/08
            TestOutputGalleryAlbum(gaBase, 0);

            // Before populating the tree, we should populate the dtMediaAlbumInfo
            // This should populate the dtMediaAlbumInfo datatable with the following information:
            // AlbumID, MediaID, FullPath, FileName, ShortCaption, LongCaption, Rating
            // ExistsInGallery  - This one will need to be populated later.

            PopulateDtMediaAlbumInfo();
            //This will populate the treeview with the collections
            //Firstly get the smart collection details
            //20080905 wcf Note this seems to be the "Last 6 months" collection in my db 
            //20080905 wcf this is handled here because it shows up in elements but the entries are dynamic, not defined by a tag

            DataTable dtSmartCollTopLevel = new DataTable();
            //wcf 20080911 intSmartCollTopLevelId = GetTagId(strTNSmartColl, 0, ref dtSmartCollTopLevel);
            intCurrentCollTopLevelId = intSmartCollTopLevelId;
            //private void PopulateDtMediaAlbumInfo(int AlbumID, int MediaID, string FullPath, string FileName, string ShortCaption, string LongCaption, int Rating, bool ExistsInGallery)


            //We have the smart collection top level thing. Now to try to find the first child, and process that.
            DataTable dtSmartCollFirstChild = new DataTable();
            int smartCollFirstChildId = GetTagId(intSmartCollTopLevelId, 0, ref dtSmartCollFirstChild);
            if (smartCollFirstChildId != 0)
            {
                //Process children
                TreeNode tnDummyNode = new TreeNode();
                ProcessPSEAlbum(dtSmartCollFirstChild, ref tvAlbums, ref tnDummyNode, true);
            }
            //Now to process the normal collections
            DataTable dtCollTopLevel = new DataTable();
            intCollTopLevelId = GetTagId(strTNColl, 0, ref dtSmartCollTopLevel);
            intCurrentCollTopLevelId = intCollTopLevelId;
            //We have the normal collection top level thing. Now to try to find the first child, and process that.
            DataTable dtCollFirstChild = new DataTable();

            int collFirstChildId = GetTagId(intCollTopLevelId, 0, ref dtCollFirstChild);
            if (collFirstChildId != 0)
            {
                //Process children
                TreeNode tnDummyNode = new TreeNode();
                ProcessPSEAlbum(dtCollFirstChild, ref tvAlbums, ref tnDummyNode, true);
            }
            //20080905 wcf now add events etc.
            // DataTable dtCollTopLevel = new DataTable();
            intUserTopLevelId = GetTagId(strTNUserNS, 0, ref dtSmartCollTopLevel);
            //intCurrentCollTopLevelId = intCollTopLevelId;
            //We have the normal collection top level thing. Now to try to find the first child, and process that.
            //DataTable dtCollFirstChild = new DataTable();
            collFirstChildId = GetTagId(intUserTopLevelId, 0, ref dtCollFirstChild);
            if (collFirstChildId != 0)
            {
                //Process children
                TreeNode tnDummyNode = new TreeNode();
                ProcessPSEAlbum(dtCollFirstChild, ref tvAlbums, ref tnDummyNode, true);
            }
            // Now the treeview has been populated with the PSE6 albums. dtMediaAlbumInfo has been populated with
            // the appropriate data, except information about if the image has been uploaded. Now I need to write code that tells if
            // the media has been uploaded already.
            // HERE HERE HERE - WRITE NEW CODE HERE!

            // Here is where I am stuck. There is no efficient way of doing this - I either have to go through all of the images
            // that have been uploaded, and see if  that is in my PSE list, or go through each thing in my PSE list and see if
            // each one is uploaded.
            // I think perhaps a better way is to download the entire Gallery structure into a datatable.

            //PopulategaBase(); //This will retrieve the gallery information from Gallery2.
            //TestOutputGalleryAlbum(gaBase, 0); //Use this to test the output.
            //gaBase now contains data on the structure of the Gallery2 website. Now to update our database if the photos exist.
            // PopulateDtMediaAlbumInfo();
        }
        public void GetMostRecentAlbum(ref DataTable dtMostRecent)
        {
            //20080906 wcf Add item to the smart album Last 6 Months, then in form, allow user to filter that
            //this is the same code as the general code
            //  private void PopulateDtMediaAlbumInfo()
            //20080906 wcf add ",media_table.search_date_begin" to select to use in "last six months "
            string strEarliestDate = DateTime.Now.AddMonths(-6).ToString("yyyyMMdd") + "T000000";
            string sqlStatement = string.Format(
                "SELECT distinct mt.id as id, filename_search_index, volume_id, media_id, {0} as tag_id, 0 as media_index,  'Last 6 Months' as name, 'smartcollect' as type_name, " +
                "(select  drive_path_if_builtin as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin > ''  union select  serial || '/' as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin = '' " +
                ") || mt.full_filepath as location, " +
                "( SELECT metadata_integer_table.value FROM media_to_metadata_table INNER JOIN metadata_integer_table ON media_to_metadata_table.metadata_id = metadata_integer_table.id WHERE media_to_metadata_table.media_id = mt.id AND metadata_integer_table.description_id = {1}) as rating , " +
                "(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id WHERE mtm.media_id = mt.id AND mst.description_id = {2}) as notes, " +
                "(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id WHERE mtm.media_id = mt.id AND mst.description_id = {3}) as caption, search_date_begin as MediaDate " +
                "FROM media_table mt  " +
                "INNER JOIN tag_to_media_table t2m on mt.id=t2m.media_id " +
                "INNER JOIN tag_table tt on t2m.tag_id=tt.id " +
                "where (tt.type_name = 'collection'  " +
                "OR tt.type_name = 'user'  " +
                "OR tt.type_name = 'collection_ns'  " +
                "OR tt.type_name like 'user_p%'  " +
                "OR tt.type_name like 'user_event%'  " +
                "OR tt.type_name like 'user_misc%'  " +
                "OR tt.type_name = 'smartcollect' ) " +
                "AND (SELECT  media_id FROM tag_to_media_table WHERE  media_id = mt.id  AND tag_id = {4}) is null  " +
                "AND ((SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) is  null OR " +
                "(SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) = 0) " +
                "AND mt.search_date_begin > '{5}' order by mt.search_date_begin desc", intSmartCollFirstLevelId, intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId, strEarliestDate);
            // "order by mt.search_date_begin desc", intSmartCollFirstLevelId, intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId, strEarliestDate);
            //            string sqlStatement = string.Format(
            //"SELECT distinct m.id as id, filename_search_index, volume_id, media_id, {0} as tag_id, 0 as media_index,  'Last 6 Months' as name, 'smartcollect' as type_name, " +
            //"(select  drive_path_if_builtin as drive_location from volume_table where id = m.volume_id AND drive_path_if_builtin > ''  union select  serial || '/' as drive_location from volume_table where id = m.volume_id AND drive_path_if_builtin = '') || m.full_filepath as location, " +
            //"(SELECT metadata_integer_table.value FROM media_to_metadata_table INNER JOIN metadata_integer_table ON media_to_metadata_table.metadata_id = metadata_integer_table.id WHERE media_to_metadata_table.media_id = m.id AND metadata_integer_table.description_id = {1}) as rating , " +
            //"(SELECT s.value FROM media_to_metadata_table  d INNER JOIN metadata_string_table s ON d.metadata_id=s.id WHERE d.media_id = m.id AND s.description_id = {2}) as notes, " +
            //"(SELECT s.value FROM media_to_metadata_table  d INNER JOIN metadata_string_table s ON d.metadata_id=s.id WHERE d.media_id = m.id AND s.description_id = {3}) as caption, search_date_begin as MediaDate " +
            //"FROM media_table m  " +
            //"INNER JOIN tag_to_media_table g on m.id= g.media_id " +
            //"INNER JOIN tag_table t on g.tag_id=t.id " +
            //"where (t.type_name = 'collection'  " +
            //"OR t.type_name = 'user'  " +
            //"OR t.type_name = 'collection_ns'  " +
            //"OR t.type_name like 'user_p%'  " +
            //"OR t.type_name like 'user_event%'  " +
            //"OR t.type_name like 'user_misc%'  " +
            //"OR t.type_name = 'smartcollect' ) " +
            //"AND (SELECT  media_id FROM tag_to_media_table WHERE  media_id = m.id  AND tag_id = {4}) is null  " +
            //"AND ((SELECT media_index  FROM version_stack_to_media_table WHERE media_id = m.id) is  null OR " +
            //"(SELECT media_index  FROM version_stack_to_media_table WHERE media_id = m.id) = 0) " +
            //"AND m.search_date_begin > '{5}' " +
            //"", intSmartCollFirstLevelId, intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId, strEarliestDate);

            try
            {
                SQLiteDataAdapter daAlbumImage = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daAlbumImage.Fill(dtMostRecent);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in PopulateMostRecentAlbum while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }

        }

        #region ProcessPSEAlbum
        private void ProcessPSEAlbum(DataTable dtTagToProcess, ref TreeView tvAlbums, ref TreeNode tnParent, Boolean isTopLevel)
        {
            //Skip the stupid intell_viiv thing. I dont know why it is there.
            if (dtTagToProcess.Rows[0]["type_name"].ToString() != "collection_intel_viiv")
            {
                //Insert a new node for current album (album passed into this)
                TreeNode tnNewNode;
                if (isTopLevel)
                    tnNewNode = tvAlbums.Nodes.Add(dtTagToProcess.Rows[0]["name"].ToString());
                else
                    tnNewNode = tnParent.Nodes.Add(dtTagToProcess.Rows[0]["name"].ToString());
                //Using the Name property of the nodes to store the PSE6 tag id
                tnNewNode.Name = dtTagToProcess.Rows[0]["id"].ToString();
                arlPSEAlbumId.Add(Convert.ToInt32(dtTagToProcess.Rows[0]["id"]));
                //Right about here I think I need something to populate the ImageExists thing
                int intPhotoExistCount = PopulateImageExists(Convert.ToInt32(dtTagToProcess.Rows[0]["id"]));
                //Now to see if there are any children to process (No children of collection_intel_viiv will be processed)
                //tvAlbums.Nodes.Add(
                DataTable dtChildTag = new DataTable();
                int firstChildId = GetTagId(Convert.ToInt32(dtTagToProcess.Rows[0]["id"]), 0, ref dtChildTag);
                if (firstChildId != 0)
                {
                    ProcessPSEAlbum(dtChildTag, ref tvAlbums, ref tnNewNode, false);
                }
            }
            //Now to process any siblings
            DataTable dtSiblingTag = new DataTable();
            int siblingTagId = GetTagId(Convert.ToInt32(dtTagToProcess.Rows[0]["parent_id"]),
                Convert.ToInt32(dtTagToProcess.Rows[0]["sibling_index"]) + 1, ref dtSiblingTag);
            if (siblingTagId != 0)
                ProcessPSEAlbum(dtSiblingTag, ref tvAlbums, ref tnParent, isTopLevel);
        }
        #endregion ProcessPSEAlbum

        public string ReturnFullTreeIndex(TreeNode tnPar)
        {
            //Will return the node index back to the parent, in the format 1.2.1.2.5, where
            //The first (left most) is the parent index, and the last (right most) is the child index.
            //I need to do this, as I cant count on the text of the nodes being unique.
            //After writing this, I realised I didnt need it - but I'm going to leave it here. It might come in handy later.
            return "";

        }

        public void PopulatePhotoListFromAlbumImageList(ListView lvPhoto, uint[] FolderIdArray)
        {
            //This one I need - I'll come back for this one.
            //20080122 filling this in today
            /*
            newRow["AlbumID"] = AlbumID;
            newRow["MediaID"] = MediaID;
            newRow["FullPath"] = FullPath;
            newRow["FileName"] = FileName;
            newRow["ShortCaption"] = ShortCaption;
            newRow["LongCaption"] = LongCaption;
            newRow["Rating"] = Rating;
            newRow["ExistsInGallery"] = ExistsInGallery;
            */
            lvPhoto.Items.Clear();
            int ImageCount;
            string strFilter = "AlbumID = '" + FolderIdArray[0].ToString() + "'";
            DataRow[] displayRows = dtMediaAlbumInfo.Select(strFilter);
            //displayRows should now contain the rows we are after.
            ImageCount = displayRows.Length;

            ListViewItem objListItem;
            for (int i = 0; i < ImageCount; i++)
            {
                objListItem = lvPhoto.Items.Add(displayRows[i]["MediaID"].ToString()); //fImageId - Integer ID of image
                //TempString = TempString.Remove(TempString.Length - 1);
                //We have to remove the last character from all of these, as they are currently garbage.
                objListItem.SubItems.Add(displayRows[i]["FileName"].ToString()); //adjust //fImageOriginalFileName - Just the file name
                objListItem.SubItems.Add(displayRows[i]["FullPath"].ToString()); //fMediaFullPath - Full path and filename of the photo
                objListItem.SubItems.Add(displayRows[i]["ShortCaption"].ToString()); //fMediaShortCaption - Short caption of file.
                objListItem.SubItems.Add(displayRows[i]["Rating"].ToString());    //Should be the starts as an integer.
                objListItem.SubItems.Add(displayRows[i]["MediaDate"].ToString());    //wcf 20080919 The Date On The file. Use for Filtering

                //objListItem.ImageKey = AlbumImageList[ImageFullPathReq]; //fMediaFullPath - Full path and filename of the photo

                string ImageExistsString = displayRows[i]["ExistsInGallery"].ToString();
                int ImageIndex;
                objListItem.Checked = true;
                switch (ImageExistsString)
                {
                    case "True":
                        ImageIndex = 3; //Green
                        objListItem.Checked = false;
                        break;
                    case "False":
                        ImageIndex = 1; //Red
                        break;
                    default:
                        ImageIndex = 0; //White
                        break;
                }


                objListItem.ImageIndex = ImageIndex;
            }
        }
        public class PhotoItemInfo
        {
            public DataTable GetItemInfo()
            {
                DataTable dtItemInfo = new DataTable();
                string sqlStatement = string.Format("SELECT id, identifier,data_type,can_have_multiple_items as multi_item  FROM metadata_description_table where  data_type <> 'blob_type' order by identifier;");

                try
                {

                    SQLiteDataAdapter daItemInfo = new SQLiteDataAdapter(sqlStatement, PSEConn);
                    daItemInfo.Fill(dtItemInfo);
                }
                catch (Exception exc)
                {
                    MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                        "  Exception: " + exc.Message);
                    Trace.WriteLine("Error in GetItemInfo while excecuting SQL Statement: >" + sqlStatement + "< " +
                        " Exception: " + exc.Message);
                }
                if (dtItemInfo.Rows.Count > 0)
                {
                    return dtItemInfo;
                }
                else
                {
                    return null;
                }

            }
        }
        #region PopulateDtMediaAlbumInfo
        private void PopulateDtMediaAlbumInfo()
        {
            //   This should populate the dtMediaAlbumInfo datatable
            //wcf 20080919- old select:
            //string sqlStatement = string.Format(
            //"SELECT media_table.id, media_table.full_filepath, media_table.filename_search_index, media_table.volume_id, " +
            //"tag_to_media_table.media_id, tag_to_media_table.tag_id, tag_to_media_table.media_index, " +
            //"tag_table.id, tag_table.name, tag_table.type_name " +
            //"FROM media_table INNER JOIN tag_to_media_table on media_table.id=tag_to_media_table.media_id " +
            //"INNER JOIN tag_table on tag_to_media_table.tag_id=tag_table.id");

            //wcf 20080919 new select with the addition of search_date begin.  
            //Note that the subqueries return only the information we need (Not hidden, active member of version set, etc),
            //so there should be no need to check for validity later.

            string sqlStatement = string.Format(
            "SELECT mt.id, mt.filename_search_index, mt.volume_id, t2m.media_id,  " +
            "t2m.tag_id, t2m.media_index,  tt.name, tt.type_name, " +
            "(select  drive_path_if_builtin as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin > '' " +
            "union select  serial || '/' as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin = '') ||  mt.full_filepath as location, " +
            "( SELECT metadata_integer_table.value FROM media_to_metadata_table INNER JOIN metadata_integer_table  " +
            "ON media_to_metadata_table.metadata_id = metadata_integer_table.id WHERE media_to_metadata_table.media_id = mt.id  " +
            "AND metadata_integer_table.description_id = {0}) as rating , " +
            "(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id  " +
            "WHERE mtm.media_id = mt.id AND mst.description_id = {1}) as notes, " +
            "(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id  " +
            "WHERE mtm.media_id = mt.id AND mst.description_id = {2}) as caption, mt.search_date_begin as MediaDate " +
            "FROM media_table mt  " +
            "INNER JOIN tag_to_media_table t2m on mt.id=t2m.media_id " +
            "INNER JOIN tag_table tt on t2m.tag_id=tt.id " +
            "where (tt.type_name = 'collection'  " +
            "OR tt.type_name = 'user'  " +
            "OR tt.type_name = 'collection_ns'  " +
            "OR tt.type_name like 'user_p%'  " +
            "OR tt.type_name like 'user_event%'  " +
            "OR tt.type_name like 'user_misc%'  " +
            "OR tt.type_name = 'smartcollect' ) " +
            "AND (SELECT  media_id FROM tag_to_media_table WHERE  media_id = mt.id  AND tag_id = {3}) is null  " +
            "AND ((SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) is  null OR " +
            "(SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) = 0) " +
            "order by tag_id, media_index", intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId);

            // DataTable dtAlbumPrimary = new DataTable();
            DataTable dtAlbumImage = new DataTable();
            try
            {
                SQLiteDataAdapter daAlbumImage = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daAlbumImage.Fill(dtAlbumImage);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in PopulateDtMediaAlbumInfo while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            //wcf 20080911 We have the items in regular collections,  
            //now get the recent items and assign hem to the last 6 months smart album

            DataTable dtMostRecent = new DataTable();
            GetMostRecentAlbum(ref dtMostRecent);
            // DataTable dtAlbumImage = new DataTable();
            foreach (DataRow rows in dtMostRecent.Rows)
            {
                dtAlbumImage.NewRow();
                dtAlbumImage.ImportRow(rows);
            }
            //wcf 20080911 continue with regular processing
            int albumImageCount = dtAlbumImage.Rows.Count;
            CreateDtMediaAlbumInfo();
            /* wcf 20080911 always done earlier
             if (intNotesMetadataid == 0)
                 intNotesMetadataid = getMetadataId(strNotesMetadataIdentifier);
             if (intCaptionMetadataId == 0)
                intCaptionMetadataId = getMetadataId(strCaptionMetadataIdentifier);
             */
            for (int i = 0; i < albumImageCount; i++)
            {
                /* 20080910  un-necessary- removed in select statement
                 //Now to determine if this image should be listed
                 //Is the image hidden?
                
                 //Has the image been superceded?
                 int mediaId = Convert.ToInt32(dtAlbumImage.Rows[i]["id"]); //0 is media_table.id
                 if (MediaHidden(mediaId))
                     continue;
                 if (MediaIsOldVersion(mediaId))
                     continue;
                 //If we get to here, we know the image is current, and is not hidden.
                 Console.WriteLine("mediaId is " + i.ToString());
                 */
                PopulateDtMediaAlbumInfo(dtAlbumImage.Rows[i]);
            }
        }

        private void PopulateDtMediaAlbumInfo(DataRow drAlbumImage)
        {
            /* 20080911 unnecessary- done in the select
            //Will populate the dtMediaAlbumInfo table. Its schema is laid out in
            // CreateDtMediaAlbumInfo
            // I'm overloading it for the hell of it.
            // For some stupid reason, the drive letter is in another table!! Now to get it.
            string sqlStatement = string.Format("SELECT * FROM volume_table WHERE id = {0};", Convert.ToInt32(drAlbumImage["volume_id"]));
            DataTable dtVolume = new DataTable();
            try
            {
                SQLiteDataAdapter daVolume = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daVolume.Fill(dtVolume);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in PopulateDtMediaAlbumInfo while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            if (dtVolume.Rows.Count == 0)
                return;
            string strDrive = dtVolume.Rows[0]["drive_path_if_builtin"].ToString();
            if (string.IsNullOrEmpty(strDrive))
                return;
             */
            Int32 mediaId = Convert.ToInt32(drAlbumImage["media_id"]);
            Int32 albumId = Convert.ToInt32(drAlbumImage["tag_id"]);
            Int32 intRating = drAlbumImage["rating"] == DBNull.Value ? 0 : Convert.ToInt32(drAlbumImage["rating"]);
            /* 20080911 unnecessary- done in the select
             //string strNotes = GetMetadataSringValue(mediaId, intNotesMetadataid);
              string strCaption = GetMetadataSringValue(mediaId, intCaptionMetadataId);
             if (mediaId == 2368)
              Console.WriteLine("mediaId is " + mediaId.ToString());
             */
            bool blExists = gaBase.ImageExists(mediaId, albumId);
            //wcf 20080919 Add the MediaDate Info for filtering
            // The date SqLite date info is  ISO 8601  but a varaition that aprseexact does not recognize hence the long string
            DateTime MediaDate = DateTime.ParseExact(drAlbumImage["MediaDate"].ToString(), "yyyyMMddTHHmmss", null);
            PopulateDtMediaAlbumInfo(albumId, mediaId,
                drAlbumImage["location"].ToString(), drAlbumImage["filename_search_index"].ToString(),
                drAlbumImage["caption"].ToString(), drAlbumImage["notes"].ToString(), intRating, blExists, MediaDate);
            //This is where I am up to - trying to get the ImageExists thing to work right. This may require some work.
        }
        private void PopulateDtMediaAlbumInfo(int AlbumID, int MediaID, string FullPath,
            string FileName, string ShortCaption, string LongCaption, int Rating, bool ExistsInGallery, DateTime MediaDate)
        {
            DataRow newRow = dtMediaAlbumInfo.NewRow();
            newRow["AlbumID"] = AlbumID;
            newRow["MediaID"] = MediaID;
            newRow["FullPath"] = FullPath;
            newRow["FileName"] = FileName;
            newRow["ShortCaption"] = ShortCaption;
            newRow["LongCaption"] = LongCaption;
            newRow["Rating"] = Rating;
            newRow["ExistsInGallery"] = ExistsInGallery;
            newRow["MediaDate"] = MediaDate;
            if (ExistsInGallery)
                Console.WriteLine("Exists");
            dtMediaAlbumInfo.Rows.Add(newRow);
        }
        #endregion PopulateDtMediaAlbumInfo

        private void CreateDtMediaAlbumInfo()
        {
            dtMediaAlbumInfo = new DataTable();
            dtMediaAlbumInfo.Columns.Add("ID", typeof(System.Int32));
            dtMediaAlbumInfo.Columns.Add("AlbumID", typeof(System.Int32));
            dtMediaAlbumInfo.Columns.Add("MediaID", typeof(System.Int32));
            dtMediaAlbumInfo.Columns.Add("FullPath", typeof(System.String));
            dtMediaAlbumInfo.Columns.Add("FileName", typeof(System.String));
            dtMediaAlbumInfo.Columns.Add("ShortCaption", typeof(System.String));
            dtMediaAlbumInfo.Columns.Add("LongCaption", typeof(System.String));
            dtMediaAlbumInfo.Columns.Add("Rating", typeof(System.Int32));
            dtMediaAlbumInfo.Columns.Add("ExistsInGallery", typeof(System.Boolean));
            dtMediaAlbumInfo.Columns.Add("MediaDate", typeof(System.DateTime));

            //Set up primary key
            dtMediaAlbumInfo.PrimaryKey = new DataColumn[] { dtMediaAlbumInfo.Columns["ID"] };
            dtMediaAlbumInfo.Columns["ID"].AutoIncrement = true;
            dtMediaAlbumInfo.Columns["ID"].AutoIncrementSeed = 1;
            dtMediaAlbumInfo.Columns["ID"].ReadOnly = true;
        }

        public string ReturnAlbum(TreeView tvColl, Gallery2Interface G2int)
        {
            //Function will take in a treeview, which currently has the PSE collections in it
            //(with the one being uploaded from selected), and return the appropriate gallery album
            //to upload to. If necessary, it will create the album(s) to replicate the structure of PSE.
            TreeNode tnColl;
            tnColl = tvColl.SelectedNode;
            NameValueCollection nvColl;
            //Get the Gallery Album List
            //Debug.WriteLine("Trying to upload: " + tnColl.Text);
            nvColl = new NameValueCollection(G2int.GetAlbumList());
            Console.WriteLine("album_count: " + nvColl["album_count"]);
            int albumCount = Convert.ToInt32(nvColl["album_count"]);
            for (int i = 1; i <= albumCount; i++)
            {
                string albumParentName = "album.parent." + i.ToString();
                string albumNameKey = "album.name." + i.ToString();
                string albumTitleKey = "album.title." + i.ToString();
                //Debug.WriteLine("Album No " + i.ToString() + " album.title: " + nvColl[albumTitleKey] +
                //    " album.name: " + nvColl[albumNameKey] +
                //    " album.parent: " + nvColl[albumParentName]);
            }
            return (CreateGalleryAlbum(tnColl, nvColl, G2int, false));
            //return(" "); //Get rid of this later
        }
        #region CreateGalleryAlbum
        private string CreateGalleryAlbum(TreeNode tnCollPar, NameValueCollection nvColl, Gallery2Interface G2int, bool GalleryCreated)
        {
            //Function will retreive the matching gallery album for this PSE collection, or create it.
            bool ParentGalleryCreated = false;
            bool CreateAlbum = true;
            string ParentAlbumName = "";
            int albumCount;
            TreeNode tnCollLoc;
            //Get the parent of the current node
            tnCollLoc = tnCollPar.Parent;
            //If this is not the base level collection, then create the base level collection
            if (!tnCollPar.Level.Equals(0))
            {
                //Pass the parent back into this function
                ParentAlbumName = CreateGalleryAlbum(tnCollLoc, nvColl, G2int, ParentGalleryCreated);
            }
            else
            {
                //Find the top level gallery node, and keep going
                albumCount = Convert.ToInt32(nvColl["album_count"]);
                for (int i = 1; i <= albumCount; i++)
                {
                    string albumParentName = "album.parent." + i.ToString();
                    string albumNameKey = "album.name." + i.ToString();
                    string albumTitleKey = "album.title." + i.ToString();
                    if (nvColl[albumParentName] == "0")
                    {
                        ParentAlbumName = nvColl[albumNameKey];
                    }
                }
            }
            //Now determine the if this node exists
            //if AlbumNo is Null, then this must be the root PSE Collection. Therefore all we need to do
            //is find the root level Gallery album and return that. We know it exists - if it doesnt, something
            //is seriously wrong.
            albumCount = Convert.ToInt32(nvColl["album_count"]);
            for (int i = 1; i <= albumCount; i++)
            {
                string albumParentName = "album.parent." + i.ToString();
                string albumNameKey = "album.name." + i.ToString();
                string albumTitleKey = "album.title." + i.ToString();
                //Debug.WriteLine("Album No " + i.ToString() + " album.title: " + nvColl[albumTitleKey] +
                //    " album.name: " + nvColl[albumNameKey] +
                //    " album.parent: " + nvColl[albumParentName]);
                if ((nvColl[albumParentName] == ParentAlbumName) && (tnCollPar.Text == nvColl[albumTitleKey]))
                {
                    //This is the parent node. No need to do anything, just return the name (which is actually the id (number),
                    //not the word "Gallery"
                    GalleryCreated = false;
                    return (nvColl[albumNameKey]);
                }
            }
            //If we have gotten to here, then this is a child of something. We need to know if we need to create
            //a new Gallery album for this. (If the Parent Gallery was just created, we know we have to create all the children)
            CreateAlbum = ParentGalleryCreated;
            albumCount = Convert.ToInt32(nvColl["album_count"]);
            if (!ParentGalleryCreated)
            {
                for (int i = 1; i <= albumCount; i++)
                {
                    string albumParentName = "album.parent." + i.ToString();
                    string albumNameKey = "album.name." + i.ToString();
                    string albumTitleKey = "album.title." + i.ToString();
                    //Find all the children of the Parent
                    if (nvColl[albumParentName] == ParentAlbumName)
                    {
                        //Check if this this is the right child
                        if (tnCollPar.Text == nvColl[albumTitleKey])
                        {
                            //No need to create this album - it already exists.
                            CreateAlbum = false;
                            GalleryCreated = false;
                            return (nvColl[albumNameKey]);
                        }
                    }
                }
            }
            //If we have gotten to here, then we know that we need to create a gallery album
            //to match this PSE Collection.
            if (!G2int.CreateNewAlbum(tnCollPar.Text, "", ParentAlbumName))
            {
                //MessageBox.Show("New album " + addAlbumForm.textBoxNewAlbum.Text + " created!");
                //GetAndLoadAlbumsIntoTreeControl();
                MessageBox.Show("Error in creating album.");
            }
            //The new album has just been created, but we need to determine the name of it (the number that gallery
            //allocated to it) before exiting.
            NameValueCollection nvCollRefresh;
            //Get the Gallery Album List
            nvCollRefresh = new NameValueCollection(G2int.GetAlbumList());
            //Now to see if the if there is an album in nvCollRefresh which matches our criteria, and if it does,
            //return its "Name", else error (it really should be there).
            albumCount = Convert.ToInt32(nvCollRefresh["album_count"]);
            for (int i = 1; i <= albumCount; i++)
            {
                string albumParentName = "album.parent." + i.ToString();
                string albumNameKey = "album.name." + i.ToString();
                string albumTitleKey = "album.title." + i.ToString();
                if ((nvCollRefresh[albumParentName] == ParentAlbumName) && (nvCollRefresh[albumTitleKey] == tnCollPar.Text))
                {
                    return (nvCollRefresh[albumNameKey]);
                }
            }
            return (" ");
        }
        #endregion CreateGalleryAlbum
        public void SetCollectionTreeIcons(TreeView tvPSEColl)
        {
            //Function will take in a TreeView collection (The PSE Albums, as a treeview control.
            //It will assign a colour icon to each node, depending on its status. For bottom level nodes
            //(Nodes with no children), green means all photos have been uploaded, yellow means some have,
            //and red means none have. For non-bottom level nodes (those with children), if all its children
            //have a green icon, then so does it. If all of its children has a red icon, then so does it.
            //Else it has a yellow icon.
            Console.WriteLine("NodeCount right at the start: " + tvPSEColl.Nodes.Count);
            int PSECollCount = tvPSEColl.Nodes.Count;
            if (PSECollCount == 0)
                return;
            //AlbumList = new NameValueCollection(G2int.GetAlbumList());
            for (int i = 0; i < PSECollCount; i++)
            {
                TreeNode tnPSECollNode = tvPSEColl.Nodes[i];
                if (tnPSECollNode.Level == 0)
                {
                    //Debug.WriteLine("Processing Node top level node: " + tnPSECollNode.Text);
                    int IconColour = SetCollectionNodeIcon(tnPSECollNode, tvPSEColl, G2int);
                }
            }
        }
        private int SetCollectionNodeIcon(TreeNode tnPSEColl, TreeView tvPSEColl, Gallery2Interface G2int)
        {
            Console.WriteLine("Processing node: " + tnPSEColl.Text);
            //Function will set the colour of this nodes icon.
            //0 = White, 1 = Red, 2 = Yellow, 3 = Green
            int IconColour = 0; //Starts off as white.

            //Firstly, process any children nodes of this
            int NodeCount = tnPSEColl.Nodes.Count;
            Boolean NodeHasChildren = false;
            for (int i = 0; i < NodeCount; i++)
            {
                TreeNode tnChildPSEColl = tnPSEColl.Nodes[i];

                if (tnChildPSEColl.Parent != null)
                {
                    if (tnChildPSEColl.Parent.Name == tnPSEColl.Name)
                    {
                        NodeHasChildren = true;
                        //Current node is a child of tnColl. We need to process this.
                        Console.WriteLine("Calling self. i = " + i.ToString());
                        int ReturnIconColour = SetCollectionNodeIcon(tnChildPSEColl, tvPSEColl, G2int);
                        if (IconColour != 2) //If the IconColour is yellow, then we leave it at yellow
                        {
                            switch (ReturnIconColour)
                            {
                                case 1: // Red
                                    if (IconColour > 1) //if IconColour is currently green or Yellow
                                        IconColour = 2; //Set to Yellow
                                    else
                                        IconColour = 1; //Set to red
                                    break;
                                case 2: // Yellow
                                    IconColour = 2; //Set to Yellow
                                    break;
                                case 3: // Green
                                    if ((IconColour == 1) | (IconColour == 2)) // If IconColour is currnetly red or Yellow
                                        IconColour = 2; // Set to Yellow
                                    else
                                        IconColour = 3; // Set to Green
                                    break;
                            }
                            //if (ReturnIconColour == 2)
                            //{

                            //}
                        }
                    }
                }
            }
            //}
            //Now to process the photos of this collection, but only if there are no child nodes (as
            //PSE does not allow a collection with photos to contain other collections)
            if (!NodeHasChildren)
            {
                //At this point, AlbumList should already have a list of all the albums
                //string Album = ReturnAlbum(tvColl, G2int);
                int pseAlbumID = Convert.ToInt32(tnPSEColl.Name);
                string galleryAlbumName = gaBase.GetGalleryAlbumID(pseAlbumID);
                //String AlbumName = CheckGalleryAlbum(tnPSEColl, G2int);
                //We have the AlbumName. Now to see if the pictures match up between them.
                //Get the Gallery Album List
                //Debug.WriteLine("Trying to upload: " + tnColl.Text);
                if (galleryAlbumName != "")
                    Console.WriteLine("Here it is!!");
                //Now we have the AlbumName (which is the Gallery album ID thingo) and the matching PSE album iD (pseAlbumID)
                //We now look at them both, and see if they have the same images against them.
                string strFilter = "AlbumID = '" + pseAlbumID.ToString() + "'";
                DataRow[] displayRows = dtMediaAlbumInfo.Select(strFilter);
                //displayRows should now contain the rows we are after.
                int pseImageCount = displayRows.Length;
                int pseImagesNotUploaded = 0;
                int pseImagesUploaded = 0;
                for (int i = 0; i < pseImageCount; i++)
                {
                    int pseImageID = Convert.ToInt32(displayRows[i]["MediaID"]);
                    bool pseImageExistsInGallery = gaBase.ImageExists(pseImageID, galleryAlbumName);
                    if (!pseImageExistsInGallery)
                        pseImagesNotUploaded++;
                    else
                        pseImagesUploaded++;
                }
                if (pseImagesUploaded == 0)
                    IconColour = 1; //Red
                else if (pseImagesNotUploaded == 0)
                    IconColour = 3; //Green
                else
                    IconColour = 2; //Yellow
                /*
                NameValueCollection GalleryPhotos;
                GalleryPhotos = new NameValueCollection(G2int.GetPicturesForAlbum(galleryAlbumName));
                Console.WriteLine("Photo Count for node " + tnPSEColl.Text + " " + GalleryPhotos.Count.ToString());
                Console.WriteLine("Photo Keys Count for node " + tnPSEColl.Text + " " + GalleryPhotos.Keys.Count.ToString());
                //string ImageInfoReq = "folderid." + tnPSEColl.Name.ToString() + ".image.";
                string ImageCountReq = "folderid." + tnPSEColl.Name.ToString() + ".imagecount";
                string ImageExistsCountReq = "folderid." + tnPSEColl.Name.ToString() + ".imageexistcount";
                string AllPSEImagesExistReq = "folderid." + tnPSEColl.Name.ToString() + ".allpseimagesexist";

                int CollectionPhotoCount = Convert.ToInt32(AlbumImageList[ImageCountReq]);
                int CollectionExistsCount = Convert.ToInt32(AlbumImageList[ImageExistsCountReq]);
                string blahblah = AlbumImageList[AllPSEImagesExistReq];
                Boolean AllPSEImagesExist = Convert.ToBoolean(AlbumImageList[AllPSEImagesExistReq]);
                Console.WriteLine("Getting stufffor name: " + tnPSEColl.Name.ToString() + "CollectionPhotoCount is: " +
                    CollectionPhotoCount.ToString() + " CollectionExistsCount is: " + CollectionExistsCount.ToString());
                if (CollectionExistsCount == 0)
                    IconColour = 1; //Red
                else if (!AllPSEImagesExist)
                    IconColour = 2; //Yellow
                else
                    IconColour = 3; //Green
                */

                /*
                //For testing purposes only
                //int ImageCount = Convert.ToInt32(GalleryPhotos["image_count"]);
                //for (int i = 1; i <= ImageCount; i++)
                //{
                    string imageTitle = "image.title." + i.ToString();
                    string imageCaption = "image.caption." + i.ToString();
                    string imageName = "image.name." + i.ToString();
                    Debug.WriteLine("Image" + i.ToString() + " image.title: " + GalleryPhotos[imageTitle] +
                        " image.caption: " + GalleryPhotos[imageCaption] +
                        " album.name: " + GalleryPhotos[imageName]);
                }
                */
            }
            tnPSEColl.ImageIndex = IconColour;
            tnPSEColl.SelectedImageIndex = tnPSEColl.ImageIndex;
            return (IconColour);
        }

        //public void UpdateIconsAfterUpload(uint[] CollectionIdArray, TreeView tvPSEColl)
        //{
        //}
        public string ReturnTagsForPicture(uint pictureID, bool includePersonTags, bool includePlacesTags, bool includeEventsTags, bool includeOtherTags)
        {
            return "";
        }
        #region GetTagId
        private int GetTagId(string tagName, int parentId, ref DataTable dtTag)
        {
            string sqlStatement = string.Format("SELECT * FROM tag_table WHERE name = '{0}' AND parent_id = {1};", FormatSQLiteString(tagName.ToString()), parentId);
            return GetTagId(sqlStatement, ref dtTag);
        }
        private int GetTagId(string tagName, int parentId)
        {
            DataTable dtTag = new DataTable();
            return GetTagId(tagName, parentId, ref dtTag);
        }
        private int GetTagId(int parentId, int siblingIndex, ref DataTable dtTag)
        {
            //20080905 wcf added order by clause to pick up the rest of my albums.
            string sqlStatement = string.Format("SELECT * FROM tag_table WHERE sibling_index >= {0} AND parent_id = {1} order by sibling_index;", siblingIndex, parentId);
            return GetTagId(sqlStatement, ref dtTag);
        }
        private int GetTagId(int parentId, int siblingIndex)
        {
            DataTable dtTag = new DataTable();
            return GetTagId(parentId, siblingIndex, ref dtTag);
        }
        //private int GetTagId(string tagName, string tagTypeName)
        //{
        //    DataTable dtTag = new DataTable();
        //    return GetTagId(tagName, tagTypeName, ref dtTag);
        //}
        private int GetTagId(string tagName, string tagTypeName)
        {
            //20080908 wcf
            int intTagId = 0;
            string sqlStatement = string.Format("SELECT id FROM tag_table WHERE name = '{0}' AND type_name = '{1}';",
                   FormatSQLiteString(tagName), FormatSQLiteString(tagTypeName));
            SQLiteCommand cmd = new SQLiteCommand(sqlStatement, PSEConn);
            try
            {
                intTagId = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return intTagId;

        }
        private int GetTagId(string tagName, string tagTypeName, ref DataTable dtTag)
        {
            string sqlStatement = string.Format("SELECT * FROM tag_table WHERE name = '{0}' AND type_name = '{1}';",
                FormatSQLiteString(tagName), FormatSQLiteString(tagTypeName));
            return GetTagId(sqlStatement, ref dtTag);
        }
        private int GetTagId(string sqlStatement, ref DataTable dtTag)
        {
            dtTag = new DataTable();
            try
            {
                SQLiteDataAdapter daTag =
                    new SQLiteDataAdapter(sqlStatement, PSEConn);
                daTag.Fill(dtTag);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in GetTagId while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            if (dtTag.Rows.Count > 0)
            {
                return Convert.ToInt32(dtTag.Rows[0]["id"]);
            }
            else
            {
                return 0;
            }
        }
        #endregion GetTagId

        #region Rating
        //wcf 20080923 not needed- found in select
        //public int GetRating(int mediaId)
        //{
        //    getRatingMetadataId();
        //    string sqlStatement =
        //        string.Format("SELECT media_to_metadata_table.media_id, media_to_metadata_table.metadata_id, " +
        //        "metadata_integer_table.id, metadata_integer_table.description_id, metadata_integer_table.value " +
        //        "FROM media_to_metadata_table INNER JOIN metadata_integer_table ON media_to_metadata_table.metadata_id=metadata_integer_table.id " +
        //        "WHERE media_to_metadata_table.media_id = {0} AND metadata_integer_table.description_id = {1};", mediaId, intRatingMetadataId);
        //    DataTable dtRating = new DataTable();
        //    try
        //    {
        //        SQLiteDataAdapter daRating = new SQLiteDataAdapter(sqlStatement, PSEConn);
        //        daRating.Fill(dtRating);
        //    }
        //    catch (Exception exc)
        //    {
        //        MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
        //            "  Exception: " + exc.Message);
        //        Trace.WriteLine("Error in GetRating while excecuting SQL Statement: >" + sqlStatement + "< " +
        //            " Exception: " + exc.Message);
        //    }
        //    if (dtRating.Rows.Count == 0)
        //        return 0;
        //    return Convert.ToInt32(dtRating.Rows[0][4].ToString());


        //}
        //private void getRatingMetadataId()
        //{
        //    if (intRatingMetadataId != 0)
        //        return;
        //    intRatingMetadataId = getMetadataId(strRatingMetadataIdentifier);
        //}
        //private Int32 getMetadataId(string metadataIdentifier)
        //{
        //    string sqlStatement =
        //        string.Format("SELECT * FROM metadata_description_table WHERE identifier = '{0}';", metadataIdentifier);
        //    DataTable dtMetadataDesc = new DataTable();
        //    try
        //    {
        //        SQLiteDataAdapter daMetadataDesc = new SQLiteDataAdapter(sqlStatement, PSEConn);
        //        daMetadataDesc.Fill(dtMetadataDesc);
        //    }
        //    catch (Exception exc)
        //    {
        //        MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
        //            "  Exception: " + exc.Message);
        //        Trace.WriteLine("Error in getMetadataId while excecuting SQL Statement: >" + sqlStatement + "< " +
        //            " Exception: " + exc.Message);
        //    }
        //    if (dtMetadataDesc.Rows.Count == 0)
        //        return 0;
        //    else
        //        return Convert.ToInt32(dtMetadataDesc.Rows[0]["id"]);
        //}
        public string[] GetPSE2G2Fields(int intItemId, Pix2Gallery2Settings pix2gallery2Settings)
        {
            //20080929 wcf put all the logic to return the proper strings here- start with the meta-data stuff
            string sqlStatement =
                string.Format(" SELECT CAST(mst.value as TEXT) AS value, mdt1.identifier AS identifier, mdt1.id AS id " +
                "FROM media_to_metadata_table mtmt  " +
                "INNER JOIN  metadata_string_table mst ON mtmt.metadata_id = mst.id  " +
                "INNER JOIN  metadata_description_table mdt1 ON mst.description_id = mdt1.id " +
                "WHERE mtmt.media_id = {0} " +
                "Union " +
                "SELECT CAST(mit.value AS TEXT) AS value,  mdt1.identifier, mdt1.id " +
                "FROM media_to_metadata_table mtmt  " +
                "INNER JOIN  metadata_integer_table mit ON mtmt.metadata_id = mit.id  " +
                "INNER JOIN  metadata_description_table mdt1 ON mit.description_id = mdt1.id " +
                "WHERE mtmt.media_id = {0} " +
                "UNION " +
                "SELECT CAST(mst.value AS TEXT) AS value, mdt1.identifier, mdt1.id " +
                "FROM media_to_metadata_table mtmt  " +
                "INNER JOIN  metadata_string_table mst ON mtmt.metadata_id = mst.id  " +
                "INNER JOIN  metadata_description_table mdt1 ON mst.description_id = mdt1.id " +
                "WHERE mtmt.media_id = {0} " +
                "Union " +
                "SELECT CAST(mdt.value as TEXT) as value,  mdt1.identifier, mdt1.id " +
                "FROM media_to_metadata_table mtmt  " +
                "INNER JOIN metadata_decimal_table mdt ON mtmt.metadata_id = mdt.id  " +
                "INNER JOIN metadata_description_table mdt1 ON mdt.description_id = mdt1.id " +
                "WHERE mtmt.media_id = {0} " +
                "UNION " +
                "SELECT CAST(mddt.value AS TEXT) AS value,  mdt1.identifier, mdt1.id " +
                "FROM media_to_metadata_table mtmt  " +
                "INNER JOIN  metadata_date_time_table mddt ON mtmt.metadata_id = mddt.id  " +
                "INNER JOIN metadata_description_table mdt1 ON mddt.description_id = mdt1.id " +
                "WHERE mtmt.media_id = {0} " +
                " ;", intItemId);
            DataTable dtMetaData = new DataTable();
            try
            {
                SQLiteDataAdapter daMetaData = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daMetaData.Fill(dtMetaData);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in metadata while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            //////////////
            string[] strField = { "", "", "", "", "", "" }; // 20081001 wcf Get Both value and label value pairs- allow user to choose which to use
            string strValue = "";
            foreach (PseField2Gallery2Field pse2g2f in pix2gallery2Settings.PseField2Gallery2Fields)
            {
                foreach (DataRow dr in dtMetaData.Rows)
                {

                    if (pse2g2f.m_id == dr["id"].ToString())
                    {
                        strValue = dr["value"].ToString().Trim();
                        switch (pse2g2f.m_gallery2field)
                        {

                            case "TITLE":
                                strField[0] += strValue + ", ";
                                strField[3] += dr["identifier"].ToString() + "= " + strValue + ", ";
                                break;
                            case "SUMMARY":
                                strField[1] += strValue + ", ";
                                strField[4] += dr["identifier"].ToString() + "= " + strValue + ", ";
                                break;
                            case "DESCRIPTION":
                                strField[2] += strValue + ", ";
                                strField[5] += dr["identifier"].ToString() + "= " + strValue + ", ";
                                break;
                            default:
                                break;
                        }
                    }

                }
                if (pse2g2f.m_id == "-70" || pse2g2f.m_id == "-75") //-70 is "Return All Albums, -75 is "Return All Tags"
                {
                    string strTags;
                    string strTagType = "Albums";
                    if (pse2g2f.m_id == "-75") strTagType = "Tags";
                    strTags = GetTagsList(intItemId, strTagType);
                    switch (pse2g2f.m_gallery2field)
                    {

                        case "TITLE":
                            strField[0] += strTags;
                            strField[3] += strTagType + "= " + strTags;
                            break;
                        case "SUMMARY":
                            strField[1] += strTags;
                            strField[4] += strTagType + "= " + strTags;
                            break;
                        case "DESCRIPTION":
                            strField[2] += strTags;
                            strField[5] += strTagType + "= " + strTags;
                            break;
                        default:
                            break;
                    }
                }
            }

            return strField;
        }
        private void WalkTagsList(ref List<string> strTagNames, int intParentId, int intTopLevelId)
        {
            string sqlStatement = string.Format("Select name, parent_id from tag_table where id = {0} ;", intParentId);
            DataTable dtTagData = new DataTable();
            try
            {
                SQLiteDataAdapter daTagData = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daTagData.Fill(dtTagData);
            }
            catch (Exception exc)
            {
                Trace.WriteLine("Error in metadata while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            DataRow dr1 = dtTagData.Rows[0];
            intParentId = Convert.ToInt32(dr1["parent_id"]);
            strTagNames.Add(dr1["name"].ToString());
            if (intTopLevelId != intParentId)
            {
                WalkTagsList(ref strTagNames, intParentId, intTopLevelId);
            }

        }
        private string GetTagsList(int intItemId, string strTagType)
        {
            //20081002 wcf handle the "All ..." types
            int intTopLevelId = 0;
            if (strTagType == "Albums")
            {
                strTagType = "'collectio%'";
                intTopLevelId = intCollTopLevelId;
            }
            else if (strTagType == "Tags")
            {
                strTagType = "'user%'";
                intTopLevelId = intUserTopLevelId;
            }
            string sqlStatement = string.Format(" SELECT  tag_table.name,tag_table.parent_id " +
            "FROM tag_to_media_table " +
            "INNER JOIN tag_table ON tag_to_media_table.tag_id = tag_table.id " +
            "WHERE  type_name like " + strTagType + " AND tag_to_media_table.media_id = {0} " +
            " ;", intItemId);
            DataTable dtTagData = new DataTable();
            try
            {
                SQLiteDataAdapter daTagData = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daTagData.Fill(dtTagData);
            }
            catch (Exception exc)
            {
                Trace.WriteLine("Error in tag_table while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            string strTagsNames = "";
            if (dtTagData.Rows.Count > 0)
            {
                List<string> lsTagNames = new List<string>();
                string strPrefix = "";
                string strSuffix = "";
                string strSeparator = "";
                string strAlbum = "";
                foreach (DataRow dr1 in dtTagData.Rows)
                {
                    int intParentId = Convert.ToInt32(dr1["parent_id"]);
                    if (intTopLevelId == intParentId)
                    {
                        //20081011 wcf Its top level, so add it to string
                        strTagsNames += strSeparator + dr1["name"].ToString();
                        strSeparator = ",";
                    }
                    else
                    {
                        lsTagNames.Add(dr1["name"].ToString());

                        //20081002 wcf  walk the tree up
                        WalkTagsList(ref lsTagNames, intParentId, intTopLevelId);
                        lsTagNames.Reverse();
                        strAlbum = "(" + String.Join(",", lsTagNames.ToArray()) + ")";
                        lsTagNames.Clear();
                        strTagsNames += strPrefix + strAlbum + strSuffix;
                        strSeparator = ",";
                    }

                }
                // lsTagNames.Reverse();

            }
            return (strTagsNames.Length == 0 ? strTagsNames : strTagsNames + ",");
        }

        private Int32 getMetadataId(string metadataIdentifier)
        {
            //20080909 wcf replace datareader with executescalar
            int intTagId = 0;
            string sqlStatement =
         string.Format("SELECT * FROM metadata_description_table WHERE identifier = '{0}';", metadataIdentifier);
            SQLiteCommand cmd = new SQLiteCommand(sqlStatement, PSEConn);
            try
            {
                intTagId = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in getMetadataId while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }

            return intTagId;

        }
        #endregion Rating
        //WCF 20080922 not needed- handled in Select
        //#region PopulateIntHiddenTagId
        //private void PopulateIntHiddenTagId()
        //{
        //    intHiddenTagId = GetTagId(strTNHidden, strTagTypeNameHidden);
        //}
        //#endregion PopulateIntHiddenTagId

        //#region MediaHidden
        ///// <summary>
        ///// Determines if a given media is hidden. Returns true
        ///// if the media is hidden, false if it is visible
        ///// </summary>
        ///// <param name="mediaId">Id of media (probably image) to test</param>
        ///// <returns></returns>
        //private bool MediaHidden(int mediaId)
        //{
        //    if (intHiddenTagId == 0)
        //        PopulateIntHiddenTagId();
        //    if (intHiddenTagId == 0)
        //        return false;
        //    string sqlStatement =
        //        string.Format("SELECT * FROM tag_to_media_table WHERE media_id = {0} AND tag_id = {1};", mediaId, intHiddenTagId);
        //    DataTable dtTagToMedia = new DataTable();
        //    try
        //    {
        //        SQLiteDataAdapter daTagTomedia = new SQLiteDataAdapter(sqlStatement, PSEConn);
        //        daTagTomedia.Fill(dtTagToMedia);
        //    }
        //    catch (Exception exc)
        //    {
        //        MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
        //            "  Exception: " + exc.Message);
        //        Trace.WriteLine("Error in MediaHidden while excecuting SQL Statement: >" + sqlStatement + "< " +
        //            " Exception: " + exc.Message);
        //    }
        //    return (dtTagToMedia.Rows.Count != 0);
        //}
        //#endregion MediaHidden
        /* wcf 20080911 not needed- handled in select
        private bool MediaIsOldVersion(int mediaId)
        {
            string sqlStatement =
                string.Format("SELECT * FROM version_stack_to_media_table WHERE media_id = {0};", mediaId);
            DataTable dtVersionToMedia = new DataTable();
            try
            {
                SQLiteDataAdapter daVersionToMedia = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daVersionToMedia.Fill(dtVersionToMedia);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in MediaIsOldVersion while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            if (dtVersionToMedia.Rows.Count == 0)
                return false;
            return (Convert.ToInt32(dtVersionToMedia.Rows[0]["media_index"]) != 0);
        }

        private string GetMetadataSringValue(Int32 mediaId, Int32 descriptionId)
        {
            string sqlStatement =
                string.Format("SELECT media_to_metadata_table.media_id, media_to_metadata_table.metadata_id, " +
                "metadata_string_table.id, metadata_string_table.description_id, metadata_string_table.value " +
                "FROM media_to_metadata_table INNER JOIN metadata_string_table ON " +
                "media_to_metadata_table.metadata_id=metadata_string_table.id " +
                "WHERE media_to_metadata_table.media_id = {0} AND metadata_string_table.description_id = {1};",
                mediaId, descriptionId);
            DataTable dtMetadata = new DataTable();
            try
            {
                SQLiteDataAdapter daMetadata = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daMetadata.Fill(dtMetadata);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in GetMetadataStringValue while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }
            if (dtMetadata.Rows.Count == 0)
                return "";
            return dtMetadata.Rows[0]["value"].ToString();
        }
        */
        private int PopulateImageExists(int albumID)
        {
            //These are the lines of code that do it from ElementsInterface
            //String AlbumName = CheckGalleryAlbumFromCollectionId(FolderIdToInsert);
            //Get the images that already exist in gallery for this album
            
            //20081031 TED - Commenting out to remove a warning
            //NameValueCollection GalleryPhotos;

            //GalleryPhotos = new NameValueCollection(G2int.GetPicturesForAlbum(AlbumName));
            return 0;
        }

        private string CheckGalleryAlbumFromCollectionId(uint intPSEAlbumId)
        {
            if (intPSEAlbumId == 0)
                return ("");
            //Function will retreive the matching gallery album for this PSE collection if it exists
            //Had to re-write this function to work from an integer
            //bool ParentGalleryCreated = false; //Still here as I copied this function from above
            // bool CreateAlbum = true; //Still here as I copied this function from above
            string ParentAlbumName = "";
            //int albumCount;
            string sqlStatement = string.Format("SELECT * FROM tag_table WHERE id = '{0}';", intPSEAlbumId);
            DataTable dtTag = new DataTable();
            GetTagId(sqlStatement, ref dtTag);

            uint ParentAlbumId = Convert.ToUInt32(dtTag.Rows[0]["parent_id"]);
            //If this is not the base level collection, then keep going up the collection levels
            if ((ParentAlbumId != 0) & (ParentAlbumId != intCollTopLevelId))
            {
                ParentAlbumName = CheckGalleryAlbumFromCollectionId(ParentAlbumId);
                if (ParentAlbumName == "")
                    return ("");
            }
            else
            {
                //Find the top level node

                /*
                albumCount = Convert.ToInt32(AlbumList["album_count"]);
                for (int i = 1; i <= albumCount; i++)
                {
                    string albumParentName = "album.parent." + i.ToString();
                    string albumNameKey = "album.name." + i.ToString();
                    string albumTitleKey = "album.title." + i.ToString();
                    if (AlbumList[albumParentName] == "0")
                    {
                        ParentAlbumName = AlbumList[albumNameKey];
                    }
                }
                 */
            }
            return "";
            /*
            //Now determine the if this node exists
            //if AlbumNo is Null, then this must be the root PSE Collection. Therefore all we need to do
            //is find the root level Gallery album and return that. We know it exists - if it doesnt, something
            //is seriously wrong.
            albumCount = Convert.ToInt32(AlbumList["album_count"]);
            for (int i = 1; i <= albumCount; i++)
            {
                string albumParentName = "album.parent." + i.ToString();
                string albumNameKey = "album.name." + i.ToString();
                string albumTitleKey = "album.title." + i.ToString();
                //Debug.WriteLine("Album No " + i.ToString() + " album.title: " + nvColl[albumTitleKey] +
                //    " album.name: " + nvColl[albumNameKey] +
                //    " album.parent: " + nvColl[albumParentName]);
                if ((AlbumList[albumParentName] == ParentAlbumName) &&
                    (dtFolderTableChild.Rows[0]["fFolderName"].ToString() == AlbumList[albumTitleKey]))
                {
                    //This is the parent node. No need to do anything, just return the name (which is actually the id (number),
                    //not the word "Gallery"
                    return (AlbumList[albumNameKey]);
                }
            }
            //If we have gotten to here, then this is a child of something. We need to know if we need to create
            //a new Gallery album for this. (If the Parent Gallery was just created, we know we have to create all the children)
            CreateAlbum = ParentGalleryCreated;
            albumCount = Convert.ToInt32(AlbumList["album_count"]);
            if (!ParentGalleryCreated)
            {
                for (int i = 1; i <= albumCount; i++)
                {
                    string albumParentName = "album.parent." + i.ToString();
                    string albumNameKey = "album.name." + i.ToString();
                    string albumTitleKey = "album.title." + i.ToString();
                    //Find all the children of the Parent
                    if (AlbumList[albumParentName] == ParentAlbumName)
                    {
                        //Check if this this is the right child
                        if (dtFolderTableChild.Rows[0]["fFolderName"].ToString() == AlbumList[albumTitleKey])
                        {
                            //No need to create this album - it already exists.
                            CreateAlbum = false;
                            return (AlbumList[albumNameKey]);
                        }
                    }
                }
            }
            //If we get to here, then we know the gallery does not exist
            return ("");
             */
        }

        /// <summary>
        /// Is intended to upload information about stuff about uploaded images after uploading.
        /// I can make this neater/more efficient in the future
        /// </summary>
        public void UpdateImageInfoAfterUploading()
        {
            PopulategaBase();
            PopulateDtMediaAlbumInfo();
        }
        #region Retreive Gallery Info
        #region PopulategaBase
        /// <summary>
        /// Should populate gaBase with all albums from Gallery2.
        /// </summary>
        private void PopulategaBase()
        {
            Console.WriteLine("PopulategaBase run");
            string baseAlbumName = "";
            gaBase = new GalleryAlbum();
            NameValueCollection galleryAlbumList = new NameValueCollection(G2int.GetAlbumList()); //Retrieve albums from Gallery2
            int galleryAlbumCount = Convert.ToInt32(galleryAlbumList["album_count"]); //Get count of Albums
            //Find the top level album
            for (int i = 1; i <= galleryAlbumCount; i++)
            {
                string albumParentName = "album.parent." + i.ToString();
                string albumNameKey = "album.name." + i.ToString();
                string albumTitleKey = "album.title." + i.ToString();
                if (galleryAlbumList[albumParentName] == "0")
                {
                    baseAlbumName = galleryAlbumList[albumNameKey];
                    gaBase.AlbumID = Convert.ToInt32(baseAlbumName);
                    gaBase.AlbumTitle = galleryAlbumList[albumTitleKey];
                    gaBase.PSEAlbumID = intCollTopLevelId;
                    gaBase.AltPSEAlbumID = intSmartCollTopLevelId;
                    // 20081009 wcf Treat Tag Collections as albums
                    gaBase.PSETagAlbumID = intUserTopLevelId;
                }
                if (gaBase.AlbumID == 0)
                    continue;
                break;
            }
            PopulateGalleryAlbumImages(gaBase);
            PopulateGalleryAlbumSubAlbums(gaBase);
        }
        #endregion PopulategaBase
        #region PopulateGalleryAlbumImages
        /// <summary>
        /// Will populate the images of a given gallery album
        /// </summary>
        /// <param name="galleryImage"></param>
        private void PopulateGalleryAlbumImages(GalleryAlbum galleryAlbum)
        {
            Console.WriteLine("PopulateGalleryAlbumImages run");
            NameValueCollection galleryPhotos = new NameValueCollection();
            try
            {
                galleryPhotos = new NameValueCollection(G2int.GetPicturesForAlbum(galleryAlbum.AlbumID.ToString()));
            }
            catch (Exception)
            {
                Trace.WriteLine("Error while getting pictures for album: " + galleryAlbum.AlbumID + ", " + galleryAlbum.AlbumTitle);
            }
            int galleryAlbumImageCount = Convert.ToInt32(galleryPhotos["image_count"]);
            try
            {
                for (int i = 1; i <= galleryAlbumImageCount; i++)
                {
                    string imageThumbName = "image.thumbName." + i.ToString();
                    string imageCaption = "image.Caption." + i.ToString();
                    string imageTitle = "image.title." + i.ToString();
                    string imageExtension = "image.forceExtension." + i.ToString();
                    string galleryPhotoFileName = galleryPhotos[imageTitle];
                    galleryPhotoFileName = galleryPhotoFileName.Remove(galleryPhotoFileName.LastIndexOf('.'));
                    GalleryImage galleryImage = new GalleryImage();
                    galleryImage.ImageTitle = galleryPhotoFileName;
                    galleryImage.ImageCaption = galleryPhotos[imageCaption];
                    galleryAlbum.GalleryImages.Add(galleryImage);
                }
            }
            catch (Exception exe)
            {
                Console.WriteLine("An error occurred .");
                Console.WriteLine(exe.Message);
            }
        }
        #endregion PopulateGalleryAlbumImages
        #region PopulateGalleryAlbumSubAlbums
        /// <summary>
        /// Will populate the sub albums of a given gallery album
        /// </summary>
        /// <param name="galleryAlbum"></param>
        private void PopulateGalleryAlbumSubAlbums(GalleryAlbum galleryAlbum)
        {
            Console.WriteLine("PopulateGalleryAlbumSubAlbums AlbumTitle= " + galleryAlbum.AlbumTitle);
            // NameValueCollection galleryAlbumList = new NameValueCollection(G2int.GetAlbumList()); //Retrieve albums from Gallery2
            NameValueCollection galleryAlbumList = new NameValueCollection();
            galleryAlbumList = G2int.GetAlbumList();
            int galleryAlbumCount;
            if (galleryAlbumList == null)
            {
                galleryAlbumCount = 0;
                MessageBox.Show("Error in Getting Picture List From Gallery Album " + galleryAlbum.AlbumTitle +"\r\nPreviously Uploaded Items Can't be marked");
            }
            else
            {
                galleryAlbumCount = Convert.ToInt32(galleryAlbumList["album_count"]); //Get count of Albums

                for (int i = 1; i <= galleryAlbumCount; i++)
                {
                    string albumParentName = "album.parent." + i.ToString();
                    string albumNameKey = "album.name." + i.ToString();
                    string albumTitleKey = "album.title." + i.ToString();

                    //20081009 wcf add tags as album check //if ((galleryAlbumList[albumParentName] == galleryAlbum.AlbumID.ToString()) || ((galleryAlbum.AltPSEAlbumID != 0) && (galleryAlbumList[albumParentName] == galleryAlbum.AltPSEAlbumID.ToString())))
                    if ((galleryAlbumList[albumParentName] == galleryAlbum.AlbumID.ToString()) || ((galleryAlbum.AltPSEAlbumID != 0) && (galleryAlbumList[albumParentName] == galleryAlbum.AltPSEAlbumID.ToString())) || ((galleryAlbum.PSETagAlbumID != 0) && (galleryAlbumList[albumParentName] == galleryAlbum.PSETagAlbumID.ToString())))
                    {
                        //This album is a child - process it.
                        GalleryAlbum gaSubAlbum = new GalleryAlbum();
                        gaSubAlbum.AlbumID = Convert.ToInt32(galleryAlbumList[albumNameKey]);
                        gaSubAlbum.AlbumTitle = galleryAlbumList[albumTitleKey];
                        if (galleryAlbum.PSEAlbumID != 0)
                        {
                            gaSubAlbum.PSEAlbumID = GetTagId(gaSubAlbum.AlbumTitle, galleryAlbum.PSEAlbumID);
                            if ((gaSubAlbum.PSEAlbumID == 0) && (galleryAlbum.AltPSEAlbumID != 0))
                                gaSubAlbum.PSEAlbumID = GetTagId(gaSubAlbum.AlbumTitle, galleryAlbum.AltPSEAlbumID);
                            //20081009 wcf Add Tag collections as albums
                            if ((gaSubAlbum.PSEAlbumID == 0) && (galleryAlbum.PSETagAlbumID != 0))
                                gaSubAlbum.PSEAlbumID = GetTagId(gaSubAlbum.AlbumTitle, galleryAlbum.PSETagAlbumID);
                        }
                        //I should process the album I'm about to add now.
                        PopulateGalleryAlbumImages(gaSubAlbum);
                        PopulateGalleryAlbumSubAlbums(gaSubAlbum);
                        //Now add it.
                        galleryAlbum.GalleryAlbums.Add(gaSubAlbum);
                    }
                }
            }
        }
        #endregion PopulateGalleryAlbumSubAlbums
        #region TestOutputGalleryAlbum
        private void TestOutputGalleryAlbum(GalleryAlbum gaTest, int indent)
        {
            String indentText = "";
            for (int i = 0; i < indent; i++)
                indentText = indentText + "    ";
            Console.WriteLine(indentText + "AlbumID: " + gaTest.AlbumID.ToString());
            Console.WriteLine(indentText + "AlbumTitle: " + gaTest.AlbumTitle.ToString());
            Console.WriteLine(indentText + "PSEAlbumID: " + gaTest.PSEAlbumID.ToString());
            Console.WriteLine(indentText + "AltPSEAlbumID: " + gaTest.AltPSEAlbumID.ToString());
            if (gaBase.GalleryImages != null)
            {
                Console.WriteLine(indentText + " IMAGES");
                int imageCount = gaTest.GalleryImages.Count;
                for (int i = 0; i < imageCount; i++)
                    Console.WriteLine(indentText + "  " + gaTest.GalleryImages[i].ImageTitle + " | " + gaTest.GalleryImages[i].ImageCaption);
            }
            if (gaBase.GalleryAlbums != null)
            {
                Console.WriteLine(indentText + " SUB ALBUMS");
                int subAlbumCount = gaTest.GalleryAlbums.Count;
                for (int i = 0; i < subAlbumCount; i++)
                    TestOutputGalleryAlbum(gaTest.GalleryAlbums[i], indent + 1);
            }
        }
        #endregion TestOutputGalleryAlbum
        #endregion Retreive Gallery Info
        #region GalleryDataClasses
        private class GalleryImage
        {
            #region PrivateMembers
            private string _imageCaption;
            private string _imageTitle;
            #endregion PrivateMembers
            #region Constructors
            public GalleryImage()
            {
                _imageCaption = "";
            }
            #endregion Constructors
            #region Properties
            public string ImageCaption
            {
                get { return _imageCaption; }
                set
                {
                    if (value == null)
                        _imageCaption = "";
                    else
                        _imageCaption = value;
                }
            }
            public string ImageTitle
            {
                get { return _imageTitle; }
                set { _imageTitle = value; }
            }
            #endregion Properties
        }
        private class GalleryAlbum
        {
            #region PrivateMembers
            private int _albumID;
            private string _albumTitle;
            private List<GalleryImage> _galleryImages;
            private List<GalleryAlbum> _galleryAlbums;
            private int _pseAlbumID;
            private int _altPSEAlbumID;
            //20081009 wcf treat tag collections like albums
            private int _pseTagAlbumID;
            #endregion PrivateMembers
            #region Constructors
            public GalleryAlbum()
            {
                _galleryAlbums = new List<GalleryAlbum>();
                _galleryImages = new List<GalleryImage>();
                _albumTitle = "";
                _pseAlbumID = 0;
                _altPSEAlbumID = 0;
                _pseTagAlbumID = 0;
            }
            #endregion Constructors
            #region Properties
            public int AlbumID
            {
                get { return _albumID; }
                set { _albumID = value; }
            }
            public string AlbumTitle
            {
                get { return _albumTitle; }
                set
                {
                    if (value == null)
                        _albumTitle = "";
                    else
                        _albumTitle = value;
                }
            }
            public List<GalleryImage> GalleryImages
            {
                get { return _galleryImages; }
                set { _galleryImages = value; }
            }
            public List<GalleryAlbum> GalleryAlbums
            {
                get { return _galleryAlbums; }
                set { _galleryAlbums = value; }
            }
            public int PSEAlbumID
            {
                get { return _pseAlbumID; }
                set { _pseAlbumID = value; }
            }
            public int AltPSEAlbumID
            {
                get { return _altPSEAlbumID; }
                set { _altPSEAlbumID = value; }
            }
            //20081009 wcf Treat Tag collections as albums
            public int PSETagAlbumID
            {
                get { return _pseTagAlbumID; }
                set { _pseTagAlbumID = value; }
            }
            #endregion Properties
            public bool ImageExists(Int32 pseImageId, Int32 pseAlbumId)
            {
                if (pseAlbumId == this.PSEAlbumID)
                {
                    for (int i = 0; i < this.GalleryImages.Count; i++)
                    {
                        if (pseImageId.ToString() == this.GalleryImages[i].ImageTitle)
                            return true;
                    }
                    return false;
                }
                for (int i = 0; i < this.GalleryAlbums.Count; i++)
                {
                    bool albumHasImage = this.GalleryAlbums[i].ImageExists(pseImageId, pseAlbumId);
                    if (albumHasImage)
                        return true;
                }
                return false;
            }
            public bool ImageExists(Int32 pseImageId, string galleryAlbumID)
            {
                if (galleryAlbumID == this.AlbumID.ToString())
                {
                    for (int i = 0; i < this.GalleryImages.Count; i++)
                    {
                        if (pseImageId.ToString() == this.GalleryImages[i].ImageTitle)
                            return true;
                    }
                    return false;
                }
                for (int i = 0; i < this.GalleryAlbums.Count; i++)
                {
                    bool albumHasImage = this.GalleryAlbums[i].ImageExists(pseImageId, galleryAlbumID);
                    if (albumHasImage)
                        return true;
                }
                return false;
            }
            /// <summary>
            /// Should return the Gallery album name (an integer) from a given PSE Album ID
            /// </summary>
            /// <param name="pseAlbumID"></param>
            /// <returns></returns>
            public string GetGalleryAlbumID(Int32 pseAlbumID)
            {
                if (pseAlbumID == 0) //It really should not be zero
                    return "";
                //20081009 wcf  add Tag Collection as album condition //if ((pseAlbumID == this.PSEAlbumID) || (pseAlbumID == this.AltPSEAlbumID))
                if ((pseAlbumID == this.PSEAlbumID) || (pseAlbumID == this.AltPSEAlbumID) || (pseAlbumID == this.PSETagAlbumID))
                    return this.AlbumID.ToString();
                //If we get here, check the sub albums.
                for (int i = 0; i < this.GalleryAlbums.Count; i++)
                {
                    string returnedGalleryAlbumID = this.GalleryAlbums[i].GetGalleryAlbumID(pseAlbumID);
                    if (returnedGalleryAlbumID != "")
                        return returnedGalleryAlbumID;
                }
                return "";
            }
        }
        #endregion GalleryDataClasses
        #region SQLite Formatting
        public string FormatSQLiteString(string stringToFix)
        {
            stringToFix = stringToFix.Replace("'", "''");
            return stringToFix;
        }
        #endregion SQLite Formatting
    }
}
